SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO

PRINT '---- tblFamilies'

PRINT '-- Table title'
INSERT tblUFGTableTitles (utt_Tablename, utt_Languagename, utt_Title, utt_Description)
VALUES ('tblFamilies','Nederlands','Families','familie gegevens')

PRINT '-- Column titles'

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_ClubName','Nederlands','Clubnaam','U dient een clubnaam in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_FamilyName','Nederlands','Familienaam','U dient een familienaam in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_Adres','Nederlands','Adres','U dient een Adres in te vullen')


INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_Zipcode','Nederlands','Postcode','U dient een Postcode in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_City','Nederlands','Plaats','U dient een plaats in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_Telephone','Nederlands','Telefoonnummer','U dient een telefoonnummer in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_Email','Nederlands','Email','U dient een emailadres in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_BankAccount','Nederlands','banknummer','U dient een banknummer in te vullen')

INSERT tblUFGColumnTitles
(uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle)
VALUES
('tblFamilies','fam_Contribution','Nederlands','Contributie','Contributie huidig seizoen')

UPDATE tblUFGTables SET utb_DisplayInCategory = 'MAINTABLES' WHERE utb_TableName = 'tblFamilies'

PRINT '-- Row Rights'
INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression,urr_SQLInsertExpression,urr_SQLUpdateExpression,urr_SQLDeleteExpression)
SELECT 'tblFamilies',urr_Rolename,'fam_ClubName = dbo.fnGetClubnameByUser(@UFGUsername)',null,'fam_ClubName = dbo.fnGetClubnameByUser(@UFGUsername)','fam_ClubName = dbo.fnGetClubnameByUser(@UFGUsername)'
FROM tblUFGRoles
WHERE urr_RoleName LIKE '%Club'

INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression,urr_SQLInsertExpression,urr_SQLUpdateExpression,urr_SQLDeleteExpression)
SELECT 'tblFamilies',urr_Rolename,'1 = 1','1 = 1','1 = 1','1 = 1'
FROM tblUFGRoles
WHERE urr_RoleName LIKE '%Bond'

INSERT tblUFGRowRights
(urr_TableName,urr_RoleName,urr_SQLSelectExpression)
SELECT 'tblFamilies',urr_Rolename,'fam_FamilyName IN (SELECT prs_FamilyName FROM tblPersons WHERE prs_Username = @UFGUsername)'
FROM tblUFGRoles
WHERE urr_RoleName = 'Leden'

UPDATE tblUFGForeignKeyColumns
SET ufc_AdditionalWhere = 'dbo.fnGetClubnameByUser(@UFGUsername) IN (clb_Name, ''*'')'
WHERE ufc_ForeignKeyName = 'FK_fam_ClubName'
AND ufc_TableName = 'tblFamilies'
AND ufc_ColumnName = 'fam_ClubName'
AND ufc_ReferenceTableName = 'tblClubs'
AND ufc_ReferenceColumnName = 'clb_Name'
GO

PRINT '-- Reports'
IF EXISTS (SELECT * FROM sys.procedures WHERE name = 'spDefault_repContribution')
	DROP PROCEDURE spDefault_repContribution
GO

CREATE PROCEDURE spDefault_repContribution (@UFGUsername varchar(50))
AS
BEGIN
	SELECT dbo.fnCurrentSeason() AS rcn_Season
END 
GO

DECLARE @XSL_ID INT
INSERT INTO tblUFGDocuments (uds_DocumentName, uds_OriginalName) VALUES ('repContribution.xsl', 'repContribution.xsl')
SELECT @XSL_ID = SCOPE_IDENTITY()

DECLARE @XML_ID INT
INSERT INTO tblUFGDocuments (uds_DocumentName, uds_OriginalName) VALUES ('Contributie.xml', 'Contributie.xml')
SELECT @XML_ID = SCOPE_IDENTITY()
INSERT tblUFGTables (utb_TableName, utb_Generated, utb_DefaultSp)
VALUES ('repContribution', 1, 'spDefault_repContribution')

INSERT tblUFGColumns (ucl_TableName, ucl_ColumnName, ucl_DomainName, ucl_sequenceNumber, ucl_Visible) VALUES
('repContribution','rcn_Season','tblSeasons^ses_Season',1,1)

INSERT tblUFGColumnTitles (uct_TableName,uct_ColumnName,uct_Languagename,uct_Title,uct_SubTitle) VALUES
('repContribution','rcn_Season','Nederlands','Seizoen','Selecteer het seizoen')

INSERT tblUFGXMLReports (urp_ReportName, urp_TableName, urp_WordTemplate, urp_XSLT, urp_ReportStoredProc, urp_BoundTable)
VALUES ('repContribution', 'repContribution', @XML_ID, @XSL_ID, 'spContributionReport', 'tblFamilies')

INSERT tblUFGXMLReportTitles (uxt_ReportName, uxt_Language, uxt_Title, uxt_Tooltip)
VALUES ('repContribution', 'Nederlands', 'Contributie', 'Contributieoverzicht voor een familie')

INSERT tblUFGXMLReportRoles (uxr_ReportName, uxr_RoleName) 
VALUES ('repContribution', 'Leden')

INSERT tblUFGColumnRights (ucr_RoleName,ucr_TableName,ucr_ColumnName,ucr_SelectRight,ucr_SearchRight,ucr_InsertRight,ucr_UpdateRight,ucr_DeleteRight,ucr_VisibleInGrid)
SELECT uxr_RoleName, urp_TableName, ucl_ColumnName,1,1,1,1,1,0
FROM tblUFGXMLReportRoles
INNER JOIN tblUFGXMLReports ON (urp_ReportName = uxr_ReportName)
INNER JOIN tblUFGColumns ON (ucl_TableName = urp_TableName)
WHERE uxr_ReportName = 'repContribution'

INSERT tblUFGXMLReportFormats (urf_ReportName, urf_Format, urf_RoleName)
VALUES ('repContribution', 'Excel', 'Leden')